
package com.scalar.chiptrack.marketing.dao;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashMap;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.marketing.MarketingForecast;
import com.scalar.chiptrack.utils.StringUtils;


/**
 *  Sales and Marketing forecast DB interactions.. Balaram Feb 29, 2004
 *
 */
public class ForecastDAO implements Constants
{

    public static HashMap loadSalesBacklog( String customer, String product, int monthFrom,
                                            int monthTo, int year, int dateCategory, int backlogType )
        throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        MarketingForecast backlog = null;
        HashMap backlogMap = new HashMap();

        if ( ( product == null ) || ( customer == null) )
        {
            return backlogMap;
        }

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "select * from  fn_ct_sls_load_backlog( ?, ?, ?, ?, ?, ?, ?, ?)" );
            pstmt.setString( 1, customer );
            pstmt.setString( 2, product );
            pstmt.setInt( 3, dateCategory );
            pstmt.setInt( 4, monthFrom );
            pstmt.setInt( 5, monthTo );
            pstmt.setInt( 6, year );
            pstmt.setString( 7, StringUtils.formatDateToString( new Date( System.currentTimeMillis() ) ) );
            pstmt.setInt( 8, backlogType );

            rset = pstmt.executeQuery();

            String reqDate = null;
            String scheduleDate = null;
            String backlogStatus = null;


            while( rset.next() )
            {

                backlog = new MarketingForecast(
                                                -1, //SF_ID
                                                rset.getString( "USR" ),
                                                rset.getString( "CUSTOMER" ),
                                                rset.getString( "PRODUCT" ),
                                                rset.getInt( "MONTH" ),
                                                null, //"MONTH_NAME"
                                                rset.getInt( "YEAR" ),
                                                rset.getInt( "QUANTITY" ),
                                                rset.getDouble( "UNIT_PRICE" ),
                                                null, //"CREATED_DATE"
                                                null,  //"MODIFIED_DATE"
                                                0
                                                );

                reqDate = rset.getString( "REQ_DATE" );
                scheduleDate = rset.getString( "SCH_DATE" );
                backlogStatus = rset.getString( "BL_TYPE" );

                if( ( ( backlogType == BACKLOG_TYPE_OPEN ) && ( "OPEN".equalsIgnoreCase( backlogStatus ) == true ) ) ||
                    ( ( backlogType == BACKLOG_TYPE_HOLD ) && ( "HOLD".equalsIgnoreCase( backlogStatus ) == true ) ) )
                {
                     backlogMap.put( (backlog.getMonth() + ( (backlog.getYear() - year)*12 ))+"", backlog );

                }
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not load Backlog details from database", sqlex);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return backlogMap;
    }


    public static HashMap loadShippedOrders( String customer, String product, int monthFrom, int monthTo, int year )
        throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        MarketingForecast shippedOrder = null;
        HashMap shippedOrdersMap = new HashMap();

        if ( ( product == null ) || ( customer == null) )
        {
            return shippedOrdersMap;
        }

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "SELECT CUSTOMER, PRODUCT, MONTH, YEAR, QUANTITY, UNIT_PRICE FROM fn_shipped_orders_by_month( ?, ?, ?, ?, ? )" );
            pstmt.setString( 1, customer );
            pstmt.setString( 2, product );
            pstmt.setInt( 3, monthFrom );
            pstmt.setInt( 4, monthTo );
            pstmt.setInt( 5, year );

            rset = pstmt.executeQuery();

            String reqDate = null;
            String scheduleDate = null;
            String backlogStatus = null;

            while( rset.next() )
            {

                shippedOrder = new MarketingForecast(
                                                -1,     //SF_ID
                                                null,   //SALES_PERSON
                                                rset.getString( "CUSTOMER" ),
                                                rset.getString( "PRODUCT" ),
                                                rset.getInt( "MONTH" ),
                                                null, //"MONTH_NAME"
                                                rset.getInt( "YEAR" ),
                                                rset.getInt( "QUANTITY" ),
                                                rset.getDouble( "UNIT_PRICE" ),
                                                null, //"CREATED_DATE"
                                                null,  //"MODIFIED_DATE"
                                                0
                                                );

                shippedOrdersMap.put( (shippedOrder.getMonth() + ( (shippedOrder.getYear() - year) * 12 ) )+"", shippedOrder );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not load shipped order details from database", sqlex);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return shippedOrdersMap;
    }



    public static HashMap loadForecastDetailsByUser( String salesPerson, String customer, String product,
                                                     int monthFrom, int monthTo, int year, String loadMarketingForecast, String endUser )
        throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        MarketingForecast forecast = null;
        HashMap forecastMap = new HashMap();
        float confidenceLevel = 0.0f;

        if ( ( product == null ) || ( customer == null) )
        {
            return forecastMap;
        }

        try
        {
            conn = DataSource.getConnection();

            if( ( loadMarketingForecast != null ) && ( loadMarketingForecast.trim().length() != 0 ) )
            {
                pstmt = conn.prepareStatement( "{ call sp_ct_mkg_load_marketing_forecast_details(?, ?, ?, ?, ?) }" );
                pstmt.setString( 1, customer );
                pstmt.setString( 2, product );
                pstmt.setInt( 3, monthFrom );
                pstmt.setInt( 4, monthTo );
                pstmt.setInt( 5, year );
            }
            else
            {
                pstmt = conn.prepareStatement( "{ call sp_ct_mkg_load_forecast_details(?, ?, ?, ?, ?, ?, ?) }" );
                pstmt.setString( 1, salesPerson );
                pstmt.setString( 2, customer );
                pstmt.setString( 3, product );
                pstmt.setInt( 4, monthFrom );
                pstmt.setInt( 5, monthTo );
                pstmt.setInt( 6, year );
                pstmt.setString(7, endUser);
            }

            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                if( ( loadMarketingForecast == null ) || ( loadMarketingForecast.trim().length() == 0 ) )
                {
                    confidenceLevel = rset.getFloat( "CONFIDENCE_LEVEL" );
                    endUser = rset.getString("ENDUSER");
                }
                forecast = new MarketingForecast(
                                rset.getInt( "ID" ),
                                rset.getString( "USER" ),
                                rset.getString( "CUSTOMER" ),
                                rset.getString( "PRODUCT" ),
                                rset.getInt( "MONTH" ),
                                rset.getString( "MONTH_NAME" ),
                                rset.getInt( "YEAR" ),
                                rset.getInt( "QUANTITY" ),
                                rset.getDouble( "UNIT_PRICE" ),
                                rset.getString( "CREATED_DATE" ),
                                rset.getString( "MODIFIED_DATE" ),
                                confidenceLevel
                                );

                forecastMap.put( (forecast.getMonth() + ( (forecast.getYear() - year)*12 ))+"" , forecast );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not load forecast details from database", sqlex);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return forecastMap;

   }



    /**
     * Loads all rows from sales_forecast group by user.
     *
     */
    public static HashMap loadCompleteForecastDetails( String customer, String product, int monthFrom, int monthTo, int year, String endUser,String mkgforecast  )
        throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        MarketingForecast forecast = null;
        MarketingForecast tempforecast = null;
        //Modified by Gandhi on 5th Aug 2004
        // HashMap replaced with LinkedHashMap
        LinkedHashMap forecastDetailsMap = new LinkedHashMap();
        LinkedHashMap forecastDetailsAndUserMap = new LinkedHashMap();
        // Modification ends here - Gandhi

        if ( ( product == null ) || ( customer == null) )
        {
            return forecastDetailsAndUserMap;
        }

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "{ call sp_ct_mkg_load_forecast_details(?, ?, ?, ?, ?, ?, ?) }" );
            pstmt.setString( 1, null/*salesPerson*/ );
            pstmt.setString( 2, customer );
            pstmt.setString( 3, product );
            pstmt.setInt( 4, monthFrom );
            pstmt.setInt( 5, monthTo );
            pstmt.setInt( 6, year );
            //added by varma 14 sep 2004
            if(mkgforecast == null || mkgforecast.trim() .length() <= 0)
            {
                pstmt.setString(7, endUser);
            }
            else
            {
                pstmt.setString(7,null);
            }

            rset = pstmt.executeQuery();

            int monthOut = -1;
            int yearOut = -1;
            int qty = 0;
            int oldQty = 0;
            double unitPrice = 0.0f;
            double oldUnitPrice = 0.0f;

            while( rset.next() )
            {
                forecast = new MarketingForecast(
                                rset.getInt( "ID" ),
                                rset.getString( "USER" ),
                                rset.getString( "CUSTOMER" ),
                                rset.getString( "PRODUCT" ),
                                rset.getInt( "MONTH" ),
                                rset.getString( "MONTH_NAME" ),
                                rset.getInt( "YEAR" ),
                                rset.getInt( "QUANTITY" ),
                                rset.getDouble( "UNIT_PRICE" ),
                                rset.getString( "CREATED_DATE" ),
                                rset.getString( "MODIFIED_DATE" ),
                                rset.getFloat( "CONFIDENCE_LEVEL" ));

                String customerName = forecast.getCustomerName();
                if(customerName.equals("0"))
                {
                    customerName = "other Customers";
                }
                qty = rset.getInt("QUANTITY");
                unitPrice = rset.getDouble( "UNIT_PRICE" );
                // Modified by Prabhanjan on 18 Jun 2004.
                //forecastDetailsMap = ( HashMap ) forecastDetailsAndUserMap.get( forecast.getSalesPerson() );
                forecastDetailsMap = ( LinkedHashMap ) forecastDetailsAndUserMap.get( forecast.getSalesPerson() + " for " + customerName );

                if( forecastDetailsMap == null )
                {
                    forecastDetailsMap = new LinkedHashMap();

                }
                //added by varma 14 sep 2004
                else
                {
                    tempforecast = (MarketingForecast)forecastDetailsMap.get( (forecast.getMonth() + ( (forecast.getYear() - year)*12 ))+"" );
                    if(tempforecast != null)
                    {
                        oldQty = tempforecast.getForecastQty() ;
                        forecast.setForecastQty( qty + oldQty );
                        oldUnitPrice =  tempforecast.getUnitPrice() ;
                        if( oldUnitPrice > unitPrice )
                        {
                            forecast.setUnitPrice( oldUnitPrice );
                        }
                    }
                }

                forecastDetailsMap.put( (forecast.getMonth() + ( (forecast.getYear() - year)*12 ))+"" , forecast );

                // Modified by Prabhanjan on 18 Jun 2004 to include customer name also in the key. The key will be a combination of sales person name and customer name.
                //forecastDetailsAndUserMap.put( forecast.getSalesPerson() , forecastDetailsMap );
                forecastDetailsAndUserMap.put( forecast.getSalesPerson() + " for " + customerName, forecastDetailsMap );

            }

        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not load forecast details from database", sqlex);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return forecastDetailsAndUserMap;
    }



    /**
     * Loads totals for marketing forecast purpose.
     *
     */
    public static HashMap loadForecastTotals( String customer, String product, int monthFrom, int monthTo, int year, String endUser, String mkgforecast )
        throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        MarketingForecast forecast = null;
        HashMap forecastTotalsMap = new HashMap();

        if ( ( product == null ) || ( customer == null) )
        {
            return forecastTotalsMap;
        }

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "{ call sp_ct_mkg_load_forecast_details(?, ?, ?, ?, ?, ?, ?) }" );
            pstmt.setString( 1, null/*salesPerson*/ );
            pstmt.setString( 2, customer );
            pstmt.setString( 3, product );
            pstmt.setInt( 4, monthFrom );
            pstmt.setInt( 5, monthTo );
            pstmt.setInt( 6, year );
           if(mkgforecast == null || mkgforecast.trim() .length() <= 0)
            {
                pstmt.setString(7, endUser);
            }
            else
            {
                pstmt.setString(7,null);
            }

            rset = pstmt.executeQuery();

            int monthOut = -1;
            int yearOut = -1;
            int qty = 0;
            int oldQty = 0;
            // Modified type float to double for price by veeru on 14th June 2004
            double unitPrice = 0.0f;
            double oldUnitPrice = 0.0f;

            while( rset.next() )
            {
                rset.getInt( "ID" );
                rset.getString( "USER" );
                rset.getString( "CUSTOMER" );
                rset.getString( "PRODUCT" );
                monthOut = rset.getInt( "MONTH" );
                rset.getString( "MONTH_NAME" );
                yearOut = rset.getInt( "YEAR" );
                qty = rset.getInt( "QUANTITY" );
                unitPrice = rset.getDouble( "UNIT_PRICE" );
                rset.getString( "CREATED_DATE" );
                rset.getString( "MODIFIED_DATE" );
                rset.getFloat( "CONFIDENCE_LEVEL" );
                rset.getString("ENDUSER");
                forecast = ( MarketingForecast ) forecastTotalsMap.get( ( monthOut + ( (yearOut - year)*12 ) )+"" );

                if( forecast == null )
                {
                    forecast = new MarketingForecast();
                    forecast.setForecastQty( qty );
                    forecast.setUnitPrice( unitPrice );
                }
                else
                {
                    oldQty = forecast.getForecastQty();
                    forecast.setForecastQty( qty + oldQty );

                    oldUnitPrice = forecast.getUnitPrice();
                    if( unitPrice > oldUnitPrice )
                    {
                        forecast.setUnitPrice( unitPrice );
                    }
                }

                forecastTotalsMap.put( ( monthOut + ( (yearOut - year)*12 ) )+"", forecast );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not load forecast details from database", sqlex);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return forecastTotalsMap;
    }


    public static void saveForecastDetails( String user, String product, String customer,
                                            int startMonth, int monthCount, int year, String[] qty,
                                            String[] unitPrice, String marketingForecast,String[] confidenceLevel, String endUser)
        throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection();
            if( ( marketingForecast != null ) && ( marketingForecast.trim().length() != 0 ) )
            {
                cstmt = conn.prepareCall( "{ Call sp_ct_mkg_save_marketing_forecast_details( ?, ?, ?, ?, ?, ?, ? ) }" );
            }
            else
            {
                cstmt = conn.prepareCall( "{ Call sp_ct_mkg_save_forecast_details( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            }

            int month = -1;
            int actualYear = -1;

            for( int i = 0; i < monthCount; i++ )
            {
                month = (startMonth + i)%12;
                actualYear = year + ((startMonth + i)/12);

                cstmt.setString( 1, user );
                cstmt.setString( 2, customer );
                cstmt.setString( 3, product );
                cstmt.setString( 4, qty[i] );
                cstmt.setString( 5, unitPrice[i] );
                cstmt.setInt( 6, month );
                cstmt.setInt( 7, actualYear );
                if( ( marketingForecast == null ) || ( marketingForecast.trim().length() == 0 ) )
                {
                    cstmt.setString( 8, confidenceLevel[i]);
                    //added by varma 09 sep 2004 to set the end user name
                    cstmt.setString( 9, endUser);
                }
                cstmt.addBatch();
            }

            cstmt.executeBatch();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not insert forecast details into database", sqle );
        }
        finally
        {
            if( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore){}
            }

            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}


            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }

    public static String getMonthName(int month) throws Exception
    {

        switch(month) {
            case 0:
                return "Jan";
            case 1:
                return "Feb";
            case 2:
                return "Mar";
            case 3:
                return "Apr";
            case 4:
                return "May";
            case 5:
                return "Jun";
            case 6:
                return "Jul";
            case 7:
                return "Aug";
            case 8:
                return "Sep";
            case 9:
                return "Oct";
            case 10:
                return "Nov";
            case 11:
                return "Dec";
            default:
                return null;
        }
    }

    // Prabhanjan, Aug 10, 2004
    public static int getMonth(String monthName)
    {
        if( monthName != null )
        {
            if( "jan".equalsIgnoreCase( monthName ) )
                return 0;
            else if( "feb".equalsIgnoreCase( monthName ) )
                return 1;
            else if( "mar".equalsIgnoreCase( monthName ) )
                return 2;
            else if( "apr".equalsIgnoreCase( monthName ) )
                return 3;
            else if( "may".equalsIgnoreCase( monthName ) )
                return 4;
            else if( "jun".equalsIgnoreCase( monthName ) )
                return 5;
            else if( "jul".equalsIgnoreCase( monthName ) )
                return 6;
            else if( "aug".equalsIgnoreCase( monthName ) )
                return 7;
            else if( "sep".equalsIgnoreCase( monthName ) )
                return 8;
            else if( "oct".equalsIgnoreCase( monthName ) )
                return 9;
            else if( "nov".equalsIgnoreCase( monthName ) )
                return 10;
            else if( "dec".equalsIgnoreCase( monthName ) )
                return 11;
        }
        return 0;
    }

    public static String getForecastTimeFrame(DBTransaction dbTransaction) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String forecasttimeframe = null;
        try{
            if(dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement("SELECT TFNAME FROM FCTFRAME WHERE TFSelect = 0");
            rset = pstmt.executeQuery();
            if(rset.next()){
                forecasttimeframe = rset.getString("TFNAME");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
             if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return forecasttimeframe;
    }
}